package test.preparestatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PrepareStatementMain {

	public static void main(String[] args){
		
		final String TAB = "\t";
		
		final String URL = "jdbc:sqlite:/Users/ ... /student";
		
		Connection conn = null;

		PreparedStatement pst = null;
		ResultSet rs = null;
		
		//insert
		String query1 = "insert into student (id, name, degree) values (?, ?, ?)";
		
		//update
		String query2 = "update student set degree = ? where id = ?";
		
		//delete
		String query3 = "delete from student where id = ?";
		
		//select
		String query4 = "select * from student where id >= ? and id < ?";
		
		int count = -1;
		
		try{
			Class.forName("org.sqlite.JDBC");
			
			conn = DriverManager.getConnection(URL);
			
			//for query1;
			///*
			pst = conn.prepareStatement(query1);
			pst.setInt(1, 101);
			pst.setString(2, "Alice");
			pst.setInt(3, 10);
			
			count = pst.executeUpdate();
			System.out.println("query1" + TAB + "count : " + count);
			//*/
			//***************************************

			//for query2;
			///*
			pst = conn.prepareStatement(query2);
			pst.setInt(1, 11);
			pst.setInt(2, 111);
			
			count = pst.executeUpdate();
			System.out.println("query2" + TAB + "count : " + count);
			//*/
			//***************************************
			
			//for query3;
			///*
			pst = conn.prepareStatement(query3);
			pst.setInt(1, 109);

			count = pst.executeUpdate();
			System.out.println("query3" + TAB + "count : " + count);
			//*/
			//***************************************
			
			//for query4;
			///*
			pst = conn.prepareStatement(query4);
			pst.setInt(1, 105);
			pst.setInt(2, 120);
			
			rs = pst.executeQuery();
			while(rs.next()){
				System.out.println(rs.getString("id") + TAB + rs.getString("name"));
			}
			//*/
			//***************************************
			
		}catch(ClassNotFoundException e1){
				e1.printStackTrace();
		}catch(SQLException e2){
				e2.printStackTrace();
		}finally{
			try{
				if(rs != null) rs.close();
				if(pst != null) pst.close();
				if(conn != null) conn.close();
			}catch(Exception e3){
				e3.printStackTrace();
			}
		}
	}
}
